package jdbc;

import org.junit.Test;

import java.sql.*;

import static org.junit.Assert.assertEquals;

public class JdbcDemo {
    private static final String URL = "jdbc:mysql://10.10.61.14:8080/jdbc";
    private static final String USER = "jdbc";
    private static final String PASSWORD = "jdbc123!";

    @Test
    public void addStudent() {
        System.out.println("插入学生信息");
        // 写 sql
        String sql = "insert into student_inf(name, gender, age)  values (?,?,?)";
        // 连接数据库
        Connection connection = null;
        // 准备 PreparedStatement
        PreparedStatement pstm = null;
        int num = 0;
        try {
            // 连接数据库
            connection = DriverManager.getConnection(URL, USER, PASSWORD);

            pstm = connection.prepareStatement(sql);

            // 设置参数， 注意参数的起始序号是 1
            pstm.setString(1, "李明");
            pstm.setString(2, "男");
            pstm.setInt(3, 15);
            // 执行 sql， 返回受影响的行数
            num = pstm.executeUpdate() ;
            System.out.println("成功");
        } catch (SQLException e) {
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
            if (pstm != null) {
                try {
                    pstm.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
            e.printStackTrace();
        }
        assertEquals(1, num);
    }

    // 查询数据
    @Test
    public void selectStudent()  {
        System.out.println("按姓名查询学生信息");
        String sql = "select * from student_inf where name = ?";
        try (Connection connection = DriverManager.getConnection(URL, USER, PASSWORD);
            PreparedStatement pstm = connection.prepareStatement(sql)) {
            pstm.setString(1, "李明");
            try (ResultSet rs = pstm.executeQuery()) {
                while (rs.next()) {
                    int id = rs.getInt(1); // 指定序号
                    String name = rs.getString("name"); // 指定列名
                    String gender = rs.getString("gender");
                    int age = rs.getInt("age");
                    System.out.println(id + "\t" + name + "\t" + gender + "\t" + age);
                }
            }
        } catch (SQLException e) {
            System.err.println("数据库错误: " + e.getMessage());
            e.printStackTrace();
        }
    }

    @Test
    public void updateStudent() {
        System.out.println("修改学生信息");
        int num = 0;
        // 按名字修改年龄
        String sql = "update student_inf set age = ? where name = ?";
        try (Connection connection = DriverManager.getConnection(URL, USER, PASSWORD);
             PreparedStatement pstm = connection.prepareStatement(sql)) {
            pstm.setInt(1, 20);
            pstm.setString(2, "李明");
            num = pstm.executeUpdate();
            System.out.println("成功");
        } catch (SQLException e) {
            System.err.println("数据库错误: " + e.getMessage());
            e.printStackTrace();
        }
        assertEquals(1, num);
    }

    @Test
    public void deleteStudent() {
        System.out.println("删除学生信息");
        int num = 0;
        String sql = "delete from student_inf where name = ?";
        try (Connection connection = DriverManager.getConnection(URL, USER, PASSWORD);
             PreparedStatement pstm = connection.prepareStatement(sql)) {
            pstm.setString(1, "李明");
            num = pstm.executeUpdate();
            System.out.println("成功");
        } catch (SQLException e) {
            System.err.println("数据库错误: " + e.getMessage());
            e.printStackTrace();
        }
        assertEquals(1, num);
    }
}
